package com.skycode6.dao;

import com.skycode6.bean.NewsBean;
import com.skycode6.util.DBConnection;
import com.skycode6.util.SkyTime;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsDao {
    DBConnection DBConn = new DBConnection();

    public List<NewsBean> getList(int page) {
        Connection conn = DBConn.getConn();
        String sql = "select * from news limit " + (page - 1) * 10 + ",10";
        List<NewsBean> list = new ArrayList<>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                NewsBean newsBean = new NewsBean();
                newsBean.setNewsID(rs.getInt("NewsID"));
                newsBean.setNewsTitle(rs.getString("NewsTitle"));
                newsBean.setNewsContent(rs.getString("NewsContent"));
                newsBean.setNewsTime(SkyTime.getDateTime());
                newsBean.setAdminName(rs.getString("AdminName"));
                list.add(newsBean);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    /**
     * @param newsBean 传入要更新新闻对象，必须包含新闻ID值
     * @return 受影响的行数
     */
    public int update(NewsBean newsBean) {
        Connection conn = DBConn.getConn();
        if (newsBean.getNewsID() > 0) {
            try {
                Statement stmt = conn.createStatement();
                String sql = "update news set NewsTitle='" + newsBean.getNewsTitle() + "'"
                        + ",NewsContent='" + newsBean.getNewsContent() + "'"
                        + ",NewsTime='" + SkyTime.getDateTime() + "'"//更新时使用当前系统时间
                        + ",AdminName='" + "假人" + "'"// todo 使用添加的管理员名称
                        + "where NewsID='" + newsBean.getNewsID() + "'";
                return stmt.executeUpdate(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        }
        return 0;
    }

    public int add(NewsBean newsBean) {
        Connection conn = DBConn.getConn();
        try {//使用预处理执行插入
            String sql = "insert into news(NewsTitle,NewsContent,NewsTime,AdminName) values(?,?,?,?)";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, newsBean.getNewsTitle());//将第一个参数设置为标题内容
            pmst.setString(2, newsBean.getNewsContent());
            pmst.setString(3, (new Date()).toString());
            pmst.setString(4, "临时管理员");
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 删除功能
     */
    public int deleteNewsById(int newsID) {
        Connection conn = DBConn.getConn();
        String sql = "delete from news where NewsID=?";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1, newsID);
            return pmst.executeUpdate();
        } catch (SQLException e) {

        }
        return 0;
    }

    public int countPage() {
        Connection conn = DBConn.getConn();
        String sql = "select count(*) from news";
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                return (rs.getInt(1) / 10) + 1;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }

    public int deleteNewsByNewsID(int id) {
        return 0;
    }

    public NewsBean getNewsById(int newsID) {
        Connection conn = DBConn.getConn();
        NewsBean newsBean = new NewsBean();
        try {
            String sql = "select * from news where NewsID=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1, newsID);
            ResultSet rs = pmst.executeQuery();
            if (rs.next()) {
                newsBean.setNewsID(rs.getInt(1));
                newsBean.setNewsTitle(rs.getString(2));
                newsBean.setNewsContent(rs.getString(3));
                newsBean.setNewsTime(rs.getString(4));
                newsBean.setAdminName(rs.getString(5));
                return newsBean;
            }
        }catch (SQLException e){
            throw new RuntimeException(e);
        }
        return newsBean;
    }
}
